package j.util.jdbc.simple.pagination;

import j.data.PagedData;
import j.util.New;
import j.util.jdbc.simple.core.SimpleJdbcContext;
import j.util.jdbc.simple.core.support.SimpleJdbcUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @author 刘军海
 * @version 2011-8-22 下午8:43:10
 *
 */
public class StandardPagination {	
	protected static Log log = LogFactory.getLog(StandardPagination.class);
	/**
	 * oracle的分页模板
	 * %1$s => 查询语句
	 * %2$d => endRow
	 * %3$d => startRow
	 */
	public static final String ORACLE_PAGING_TEMPLATE = "SELECT * FROM (" +
			"SELECT ROWNUM rn,TBL_ORACLE_PAGINATION_.* FROM(" +
			"	%1$s) TBL_ORACLE_PAGINATION_ WHERE ROWNUM<=%2$d) WHERE rn>=%3$d";
	
	/**
	 * MySql的分页模板
	 * %1$s => 查询语句
	 * %2$d => offset 记录行的偏移量，以0为基数
	 * %3$d => rows 页大小
	 */
	public static final String MYSQL_PAGING_TEMPLATE = "%1$s LIMIT %2$d,%3$d";
	//select * from (select row_number()over(order by order_template) as rn_,mssql_page_t1.* from (
	//select top 10 *,0 order_template from Customers order by CustomerID) mssql_page_t1) mssql_page_t2 where mssql_page_t2.rn_>=1
	public static final String MSSQL_PAGING_TEMPLATE = "SELECT * FROM (" +
			"SELECT ROW_NUMBER()OVER(ORDER BY MSSQL_PAGE_T1.ORDER_TEMPLATE) AS RN_,MSSQL_PAGE_T1.* FROM (" +
			"%1$s) MSSQL_PAGE_T1) MSSQL_PAGE_T2 WHERE MSSQL_PAGE_T2.RN_>=%2$d";
	/**
	 * 根据获取数据的sql语句换算出来计算总数的sql语句
	 * @param sqlFetchRows
	 * @return
	 */
	private static String getSqlCountRows(String sqlFetchRows){
		Pattern p = Pattern.compile("SELECT.+FROM",Pattern.CASE_INSENSITIVE);
		Matcher m = p.matcher(sqlFetchRows);
		StringBuffer sb = new StringBuffer();
		if(m.find()){
			m.appendReplacement(sb, "SELECT COUNT(1) FROM");
		}
		m.appendTail(sb);
		//将order by去掉
		String sql = sb.toString();
		int index = sql.toUpperCase().indexOf("ORDER BY");
		if(index>-1){
			sql = sql.substring(0,index);
		}
		return sql;
	}

	public static <T> PagedData<List<T>> fetchPage(Connection connection, Class<T> requiredType,
			String sqlFetchRows, Object[] args,
			int pageIndex, int pageSize)throws SQLException{
		String sqlCountRows = getSqlCountRows(sqlFetchRows);
		log.info(String.format("begin fetchPage query with fetchrows sql:%s.",sqlFetchRows));
		Set<String> dbColumns = New.set();
        //先查出总数
        log.info("first count total rows with sql:"+sqlCountRows+".");
        PreparedStatement stmt = connection.prepareStatement(sqlCountRows);
        if(null!=args){
            for(int i=0;i<args.length;i++){
                stmt.setObject(i+1, args[i]);
            }
        }
        ResultSet rs = stmt.executeQuery();
        int rowCount = 0;
        if(rs.next()){
            rowCount = rs.getInt(1);
        }
        if(rowCount>0){
            //查询的有数据，接着查询出分页的数据
            //计算共有多少页
            log.info("total rows:"+rowCount+" and query current page items.");
            List<T> list = New.list();
            rs.close();stmt.close();
            //fetch a single page of results
            String sql = formatCorrtedFetchDataSql(connection,sqlFetchRows,pageIndex,pageSize);
            stmt = connection.prepareStatement(sql);
            if(null!=args){
                for(int i=0;i<args.length;i++){
                    stmt.setObject(i+1, args[i]);
                }
            }
            rs = stmt.executeQuery();
            while(rs.next()){
                list.add(SimpleJdbcUtils.fromResultSetToPojo(requiredType, rs));
            }
            rs.close();
            stmt.close();
            return new PagedData<List<T>>(pageIndex,pageSize,rowCount,list);
        }
		return new PagedData<List<T>>(pageIndex,pageSize,0,null);
	}

	public static String formatCorrtedFetchDataSql(Connection conn,String sqlFetchRows,
			int pageIndex, int pageSize) {
		final int startRow = (pageIndex-1)*pageSize+1;
		int endRow = pageIndex * pageSize;
		String sql = "";
		int dbProvider = SimpleJdbcContext.getDbProvider();
		if(dbProvider == SimpleJdbcContext.ORACLE_PROVIDER){
			sql = String.format(ORACLE_PAGING_TEMPLATE,sqlFetchRows,endRow,startRow);
		}else if(dbProvider == SimpleJdbcContext.MYSQL_PROVIDER){
			sql = String.format(MYSQL_PAGING_TEMPLATE,sqlFetchRows, startRow-1,pageSize);
		}else if(dbProvider == SimpleJdbcContext.MSSQL_PROVIDER){
			/*
			 * 先将sqlFetchRows格式成如下格式：
			 * select top $endRow *,0 order_template from Customers order by CustomerID
			 */
			String _sql = sqlFetchRows.toUpperCase();
			Pattern p = Pattern.compile("SELECT(.+)FROM");
			Matcher m = p.matcher(_sql);
			if(m.find()){
				String s = m.group(1);
				sql = _sql.replaceFirst(s, " TOP "+endRow+" "+s+",0 ORDER_TEMPLATE ");
				//套用微软数据库的引擎
				sql = String.format(MSSQL_PAGING_TEMPLATE, sql,startRow);
			}
		}else{
			//默认按oracle的标准执行
			sql = String.format(ORACLE_PAGING_TEMPLATE,sqlFetchRows,endRow,startRow);
		}
		return sql;
	}
}
